package com.crl.rcdp.sys.dao;

import com.cars.util.bean.ComboboxVO;
import com.cars.util.code.GlobalConst;
import com.cars.util.page.PageUtil;
import com.cars.util.string.StringUtil;
import com.crl.dao.BaseDao;
import com.crl.rcdp.sys.model.SysRole;
import com.crl.rcdp.sys.vo.SysRoleSearchVO;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @description:
 * @author: 孔垂云
 * @version: 1.0
 * @date: 2020/6/21 17:05
 */
@Repository
public class SysRoleDao2 extends BaseDao<SysRole, SysRoleSearchVO> {
    /**
     * 查询全部，用于选择角色
     */
    public List<SysRole> list(SysRoleSearchVO sysRoleSearchVO) {
        String sql = "SELECT id,role_code,role_name,created_at,modified_at,role_desc,role_type,created_by,modified_by FROM td_sys_role where 1=1 ";
        sql += createSQL(sysRoleSearchVO);
        sql += " order by created_at";
        sql = PageUtil.createOraclePageSQL(sql, sysRoleSearchVO.getPage());
        return list(sql, sysRoleSearchVO);
    }

    /**
     * 增加角色
     */
    public int add(SysRole sysRole) {
        String sql = "INSERT INTO td_sys_role (id,role_code,role_name,role_desc,role_type,created_at,created_by)" +
                " VALUES(seq_td_sys_role.nextval,:roleCode,:roleName,:roleDesc,:roleType,sysdate,:createdBy)";
        return insertForId(sql, sysRole, "id");
    }

    /**
     * 添加角色和资源关系表
     */
  /*  public int[] batchResourceId(List<RoleResource> roleResources) {
        String sql = "INSERT INTO crl_base_role_resource(role_id,resource_id) VALUES (:roleId,:resourceId)";
        return getNamedParameterJdbcTemplate().batchUpdate(sql, SqlParameterSourceUtils.createBatch(roleResources));
    }*/

    /**
     * 校验角色代码
     */
    public SysRole checkRoleCode(String roleCode) {
        String sql = "SELECT id,role_code FROM td_sys_role WHERE role_code=?";
        return get(sql, roleCode);
    }

    /**
     * 根据角色id删除
     */
    public int delete(Integer roleId) {
        String sql = "DELETE FROM td_sys_role WHERE id=?";
        return update(sql, roleId);
    }

    /**
     * 根据角色id删除角色资源对应关系表中数据
     */
    public int deleteResourcesRef(Integer roleId) {
        String sql = "DELETE FROM crl_base_role_resource WHERE role_id =?";
        return update(sql, roleId);
    }

    /**
     * 根据角色id修改
     */
    public int update(SysRole sysRole) {
        String sql = "UPDATE td_sys_role SET role_code=:roleCode,role_name=:roleName,role_desc=:roleDesc,role_type=:roleType,modified_at=sysdate,modified_by=:modifiedBy WHERE id=:id ";
        return update(sql, sysRole);
    }

    /**
     * 下拉框
     *
     * @return
     */
    public List<ComboboxVO> listCombo() {
        String sql = "SELECT id value ,role_name label FROM td_sys_role where 1=1 order by created_at";
        return this.jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(ComboboxVO.class));
    }

    /**
     * 查询全部，用于选择角色
     */
    public List<SysRole> listAll() {
        String sql = "SELECT id,role_code,role_name,created_at,modified_at,role_desc,role_type,created_by,modified_by FROM td_sys_role where 1=1 order by created_at";
        return list(sql);
    }

    /**
     * 查询全部，用于选择角色
     */
    public List<SysRole> listRoleType() {
        String sql = "SELECT id,role_code,role_name,created_at,modified_at,role_desc,role_type,AUTH_SYSTEM_ID FROM crl_base_role where role_type=" + GlobalConst.COMMON_ROLE_TYPE + " order by created_at";
        return list(sql);
    }

    /**
     * 查询全部，用于选择角色
     */
    public List<SysRole> listRoleType(Integer authSystemId) {
        String sql = "SELECT id,role_code,role_name,created_at,modified_at,role_desc,role_type,AUTH_SYSTEM_ID FROM crl_base_role where role_type=" + GlobalConst.COMMON_ROLE_TYPE + " and auth_system_id=? order by created_at";
        return list(sql, authSystemId);
    }

    /**
     * 根据id查询
     */
    public SysRole getById(Integer roleId) {
        String sql = "SELECT id,role_code,role_name,created_at,modified_at,role_desc,role_type,AUTH_SYSTEM_ID FROM td_sys_role WHERE id=?";
        return get(sql, roleId);
    }

    /**
     * 根据角色代码获取角色
     */
    public SysRole getByCode(String roleCode) {
        String sql = "SELECT id,role_code,role_name,created_at,modified_at,role_desc,role_type,AUTH_SYSTEM_ID FROM crl_base_role WHERE role_code=?";
        return get(sql, roleCode);
    }

    /**
     * 根据角色id获取对应的资源id信息
     */
   /* public List<RoleResource> getByRoleId(Integer roleId) {
        String sql = "SELECT role_id,resource_id FROM crl_base_role_resource WHERE role_id=?";
        return jdbcTemplate.query(sql, new Object[]{roleId}, BeanPropertyRowMapper.newInstance(RoleResource.class));
    }*/


    /**
     * 按条件查询
     */
  /*  public List<SysRole> listRole(RoleSearchVO roleSearchVO) {
        String sql = "SELECT id,role_code,role_name,created_at,modified_at,role_desc,role_type,AUTH_SYSTEM_ID FROM crl_base_role WHERE 1=1 ";
        sql += createSQL(roleSearchVO);
        sql = PageUtil.createOraclePageSQL(sql, roleSearchVO.getPageIndex(), roleSearchVO.getPageSize());
        return list(sql, roleSearchVO);
    }*/

    /**
     * 统计查询数据条数
     */
    public int count(SysRoleSearchVO sysRoleSearchVO) {
        String sql = "SELECT COUNT(id) FROM td_sys_role WHERE 1=1 ";
        sql += createSQL(sysRoleSearchVO);
        return count(sql, sysRoleSearchVO);
    }

    /**
     * 拼接查询条件
     */
    private String createSQL(SysRoleSearchVO sysRoleSearchVO) {
        String sql = "";
        /*if (StringUtil.isNotNullOrEmpty(roleSearchVO.getRoleName())) {
            roleSearchVO.setRoleName(roleSearchVO.getRoleName() + "%");
            sql += " AND role_name LIKE :roleName ";
        }
        if (StringUtil.isNotNullOrEmpty(roleSearchVO.getRoleType())) {
            sql += " and role_type = :roleType";
        }
        if (roleSearchVO.getAuthSystemId() != null) {
            sql += " and auth_system_id = :authSystemId";
        }*/
        if (StringUtil.isNotNullOrEmpty(sysRoleSearchVO.getRoleName())) {
            sql += " and role_name like  '%' ||:roleName|| '%' ";
        }
        return sql;
    }

    /**
     * 根据用户id获取用户拥有角色列表
     */
    public List<SysRole> getRoleByUserId(Integer userId) {
        String sql = "SELECT id,role_code,role_name,created_at,modified_at,role_desc,role_type,AUTH_SYSTEM_ID FROM crl_base_role WHERE id IN (SELECT role_id FROM crl_base_user_role WHERE user_id=?)";
        return list(sql, userId);
    }

}
